package app.models;

import app.database.MainDatabase;
import app.database.MultimediaDBItem;
import framework.multimedia.FRDMultimediaDBItem;
import framework.multimedia.FRDMultimediaModel;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import javax.imageio.ImageIO;
import javax.swing.ImageIcon;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.ord.im.OrdImage;
import oracle.sql.BLOB;

/**
 *
 * @author Lukáš Marek
 */
public class MultimediaModel implements FRDMultimediaModel
{
	private MainDatabase db;
	
	public MultimediaModel(MainDatabase db)
	{
		this.db = db;
	}
	
	@Override
	public LinkedList getData(LinkedList<String> params)
	{
		LinkedList<FRDMultimediaDBItem> list = new LinkedList<>();
		int page = Integer.parseInt(params.get(0));
		int showingItems = Integer.parseInt(params.get(1));
		
		//zpracovani parametru filtru
		String where = "";
		boolean testImage = false;
		if (!params.get(2).equals(""))
		{
			where += " AND name LIKE '%" + params.get(2) + "%'";
		}
		if (!params.get(3).equals(""))
		{
			testImage = insertTemporalImage(params.get(3));			
			if (testImage)
			{
				where += " AND id IN (SELECT * FROM TABLE (get_similiar))";
			}
		}
		String sqlPrefix = "SELECT * FROM (SELECT R.*, ROWNUM rnum FROM (";
		String sql = "SELECT * FROM monster_tab WHERE picture IS NOT NULL";
		String sqlSufix = ") R WHERE ROWNUM <= ?) WHERE rnum >= ?";
		if (!where.equals("")) sql += where;
		OracleResultSet rset;
		OraclePreparedStatement stmt;
		try
		{
			stmt = (OraclePreparedStatement) db.conn.prepareStatement(sqlPrefix+sql+sqlSufix);
			page = (page - 1)*showingItems + 1;
			stmt.setInt(1, page + showingItems - 1);
			stmt.setInt(2, page);
			rset = (OracleResultSet) stmt.executeQuery();
			while (rset.next())
			{
				FRDMultimediaDBItem item = new FRDMultimediaDBItem();
				item.id = rset.getInt("id");
				item.name = rset.getString("name");
				OrdImage imgTmp;
				try
				{
					imgTmp = (OrdImage) rset.getORAData("picture", OrdImage.getORADataFactory());
					if (imgTmp != null)
					{
						BLOB blob = (BLOB) imgTmp.getBlobContent();
						int length = (int) blob.length();
						byte[] mybytes = blob.getBytes(1, length);
						ImageIcon img = new ImageIcon(mybytes);
						item.image = img;
					}
					else
					{
						item.image = null;
					}
				}
				catch(Exception e)
				{
					System.err.println("chyba pri stahovani obrazku");
					item.image = null;
				}
				if (item.image != null)
				{
					list.add(item);
				}
			}
			rset.close();
			stmt.close();
			
			if (testImage)
			{
				db.executeUpdate( "BEGIN"
												+ "execute immediate 'DROP TABLE testTable';"
												+ "exception when others then null;"
												+ "END;");
			}
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
		return list;
	}
	
	@Override
	public int getDataCount(LinkedList<String> params)
	{
		int count = 0;
		String where = "";
		boolean testImage = false;
		if (!params.get(2).equals(""))
		{
			where += " AND name LIKE '%" + params.get(2) + "%'";
		}
		if (!params.get(3).equals(""))
		{
			testImage = insertTemporalImage(params.get(3));			
			if (testImage)
			{
				where += " AND id IN (SELECT * FROM TABLE (get_similiar))";
			}
		}
		
		String sql = "SELECT COUNT(*) FROM monster_tab WHERE picture IS NOT NULL";
		if (!where.equals("")) sql += where;
		OracleResultSet rset;
		OraclePreparedStatement stmt;
		try
		{
			stmt = (OraclePreparedStatement) db.conn.prepareStatement(sql);
			rset = (OracleResultSet) stmt.executeQuery();
			rset.next();
			count = rset.getInt(1);
			rset.close();
			stmt.close();
			if (testImage)
			{
				db.executeUpdate( "BEGIN"
												+ "execute immediate 'DROP TABLE testTable';"
												+ "exception when others then null;"
												+ "END;");
			}
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
		return count;
	}

	@Override
	public FRDMultimediaDBItem loadObject(int id)
	{
		MultimediaDBItem item = new MultimediaDBItem();
		String sql = "SELECT * FROM monster_tab WHERE id = " + id;
	
		OracleResultSet rset;
		OraclePreparedStatement stmt;
		try
		{
			stmt = (OraclePreparedStatement) db.conn.prepareStatement(sql);
			rset = (OracleResultSet) stmt.executeQuery();
			rset.next();
			item.id = rset.getInt("id");
			item.name = rset.getString("name");
			item.lvl = rset.getString("lvl");
			item.description = rset.getString("description");
			OrdImage imgTmp;
			try
			{
				imgTmp = (OrdImage) rset.getORAData("picture", OrdImage.getORADataFactory());
				if (imgTmp != null)
				{
					BLOB blob = (BLOB) imgTmp.getBlobContent();
					int length = (int) blob.length();
					byte[] mybytes = blob.getBytes(1, length);
					ImageIcon img = new ImageIcon(mybytes);
					item.image = img;
				}
				else
				{
					item.image = null;
				}
			}
			catch(Exception e)
			{
					System.err.println("chyba pri stahovani obrazku");
					item.image = null;
			}
			rset.close();
			stmt.close();
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}		
		return item;
	}

	@Override
	public int saveObject(FRDMultimediaDBItem item) throws SQLException
	{
		int id = item.id;
		MultimediaDBItem itm = (MultimediaDBItem) item;
		ResultSet res;
		PreparedStatement stm;
		if (id == -1)
		{
			stm = db.conn.prepareStatement("INSERT INTO monster_tab (name,lvl,description, picture) VALUES (?, ?, ?, ORDSYS.ORDImage.init())");
			stm.setString(1, itm.name);
			stm.setString(2, itm.lvl);
			stm.setString(3, itm.description);
			stm.executeUpdate();
			res = db.executeQuery("SELECT monsterID_s.currval FROM DUAL");
			res.next();
			id = res.getInt("currval");
		}
		else
		{
			stm = db.conn.prepareStatement("UPDATE monster_tab SET name = ?, lvl = ?, description = ? WHERE id = ?");
			stm.setString(1, itm.name);
			stm.setString(2, itm.lvl);
			stm.setString(3, itm.description);
			stm.setInt(4, id);
			if (stm.executeUpdate() != 1)
			{
				System.err.println("Editování chyba");
				throw new SQLException("saveObject() edit chyba");
			}
		}
		
		if (itm.image != null)
		{			
			OracleResultSet rset;
			OraclePreparedStatement pstmt;
			OrdImage imgProxy;
			boolean autoCommit = db.conn.getAutoCommit();
			try 
			{
				db.conn.setAutoCommit(false);
				String Stmt = "SELECT picture FROM monster_tab WHERE id = " + id + " for update";

				rset = (OracleResultSet)db.executeQuery(Stmt);
				rset.next();
				imgProxy = (OrdImage) rset.getORAData("picture", OrdImage.getORADataFactory());
				rset.close();
			
				BufferedImage bimg = new BufferedImage(itm.image.getIconWidth(), itm.image.getIconHeight(), BufferedImage.TYPE_INT_RGB);
				itm.image.paintIcon(null, bimg.getGraphics(), 0, 0);
				ByteArrayOutputStream baos = new ByteArrayOutputStream();
				ImageIO.write(bimg, "png", baos);
				imgProxy.loadDataFromByteArray(baos.toByteArray());
				imgProxy.setProperties();

				pstmt = (OraclePreparedStatement) db.conn.prepareStatement("UPDATE monster_tab SET picture = ? WHERE id = ?");
				pstmt.setORAData(1, imgProxy);
				pstmt.setInt(2, id);
				pstmt.executeUpdate();
				db.conn.commit();
				pstmt.close();
			}
			catch (IOException ex)
			{
				System.err.println(ex.getMessage());
			}
			finally 
			{
				db.conn.setAutoCommit(autoCommit);
			}
		}
		return id;
	}

	/**
	 * odstaneni zaznamu v databazi
	 * @param id 
	 */
	public void deleteObject(int id)
	{
		try
		{
			db.executeUpdate("DELETE FROM monster_tab WHERE id = " + id);
		}
		catch (SQLException ex)	{	System.err.println(ex.getMessage()); }
	}
	
	/**
	 * vlozeni obrazku do docasne tabulky
	 * @param file
	 * @return 
	 */
	private boolean insertTemporalImage(String file)
	{
		OracleResultSet rset;
		OraclePreparedStatement pstmt;
		boolean autoCommit;
		try
		{
			db.executeUpdate("BEGIN"
										 + "execute immediate 'DROP TABLE testTable';"
										 + "exception when others then null;"
										 + "END;");
			db.executeUpdate("CREATE GLOBAL TEMPORARY TABLE testTable (img ORDImage) ON COMMIT PRESERVE ROWS");
			db.executeUpdate("INSERT INTO testTable (img) VALUES (ORDSYS.ORDImage.init())");

			OrdImage imgProxy;
			autoCommit = db.conn.getAutoCommit();
			db.conn.setAutoCommit(false);		

			rset = (OracleResultSet) db.executeQuery("SELECT img FROM testTable WHERE ROWNUM = 1 for update");
			rset.next();
			imgProxy = (OrdImage) rset.getORAData("img", OrdImage.getORADataFactory());
			rset.close();
			
			imgProxy.loadDataFromFile(file);
			imgProxy.setProperties();
			pstmt = (OraclePreparedStatement) db.conn.prepareStatement("UPDATE testTable SET img = ? WHERE ROWNUM = 1");
			pstmt.setORAData(1, imgProxy);
			pstmt.executeUpdate();
			db.conn.commit();
			pstmt.close();
			db.conn.setAutoCommit(autoCommit);
		}
		catch (IOException | SQLException ex)
		{
			System.err.println(ex.getMessage());
			return false;
		}
		return true;
	}

	
}
